//
//  lxDBAccessor.m
//  luodansc
//
//  Created by 倫 星 on 2012/10/20.
//  Copyright (c) 2012年 方正株式会社. All rights reserved.
//

#import "lxDBAccessor.h"
#import "lxSql.h"
#import "lxStringUtility.h"

static lxDBAccessor * _lxDBAccessor;

@interface lxDBAccessor(PrivateFunction)
- (BOOL)isRecordExisit:(NSString *)tableName where:(NSString *)where;
- (BOOL)isRecordExisit:(NSString *)strsql;
- (BOOL)tableByQuery:(NSString *)quereySql result:(char ***)result pnRow:(int *)pnRow pnColumn:(int *) pnColumn pzErmsg:(char **)pzErmsg;
- (void)freeQureyResultTable:(char **)result;//调用查询结果后，必须调用该函数进行内存释放
- (BOOL)dataFileFromAppToTempDir:(NSString *)fullFileName fileName:(NSString *)fileName fileExec:(NSString *)fileExec;
- (NSString *)Null2NSString:(sqlite3_stmt *)stmt column:(NSInteger)col;
- (NSString *)Nil2String:(NSString *)theString;
- (int)step:(sqlite3_stmt *)stmt;

@end

@implementation lxDBAccessor
+ (lxDBAccessor *)sharedLxDBAccessor{
   	if (nil == _lxDBAccessor) {
		_lxDBAccessor = [[lxDBAccessor alloc] init];
		_lxDBAccessor.database = nil;
		_lxDBAccessor.isSqlXmlParserd = NO;
		_lxDBAccessor.isDbOpend = NO;
		_lxDBAccessor.sqlDictionary = nil;
	}
	return _lxDBAccessor;
}

- (void)dealloc {
    [_strCurrentKey release];//must release
	[_sqlDictionary release];
    [super dealloc];
}

- (BOOL)openDataBase{
	
	if (_isDbOpend) {
		return YES;
	}
	
	NSFileManager * fileManger = [NSFileManager defaultManager];
	NSString * dbFilePath = [[lxPathUtility db3Path] stringByAppendingPathComponent:DBFileName];
	if ([fileManger fileExistsAtPath:dbFilePath]) {//数据库文件存在
		NSLog(@"DataBase file have already existed.");
	}
	else {
		[self dataFileFromAppToTempDir:DBFileName fileName:@"luodansc" fileExec:@"db3"];
	}
    
	int dbResult = sqlite3_open([dbFilePath UTF8String],&_database);
	if (dbResult!= SQLITE_OK) {
		[self closeDataBase];
		NSAssert(0,@"failed to open database");
	}
	else {
		_isDbOpend = YES;
	}
	return _isDbOpend;
}

- (void)closeDataBase{
	_isDbOpend = NO;
	sqlite3_close(_database);
}

- (NSString *)Null2NSString:(sqlite3_stmt *)stmt column:(NSInteger)col {
	char * str = (char*)sqlite3_column_text(stmt, col);
	NSString *item;
	if (str && strlen(str) > 0)
	{
		item = [NSString stringWithUTF8String:str];
	}
	else{
		// handle case when object is NULL, for example set result to empty string:
		item = nil;
	}
	return item;
}

- (BOOL)execute:(NSString *)sqlStatement {
    if (_isDbOpend == NO) {
		return NO;
	}

    char *error = nil;
    int ret = sqlite3_exec (_database,[sqlStatement UTF8String], nil, nil, &error);
    NSLog(@"lun execute sqlStatement=%@, ret=%d, SQLITE_OK=%d", sqlStatement, ret, SQLITE_OK);
	if (ret != SQLITE_OK) {
		//NSAssert1(0,@"failed to update the database because:%s",errorMsg);
        NSLog(@"lun failed to update the database because:%s", error);
		return NO;
	}
	return YES;
}

- (int)prepare:(NSString *)sqlStatement outStatement:(sqlite3_stmt **)outStatement{
	return sqlite3_prepare_v2(_database,[sqlStatement UTF8String],-1,outStatement,nil);
}

- (int)step:(sqlite3_stmt *)stmt{
	return sqlite3_step(stmt);
}

- (BOOL)tableByQuery:(NSString *)keyWords result:(char ***)result pnRow:(int *)pnRow pnColumn:(int *) pnColumn pzErmsg:(char **)pzErmsg{
	if (sqlite3_get_table(_database, [keyWords UTF8String],result, pnRow,pnColumn,pzErmsg) != SQLITE_OK) {
		return NO;
	}
	return YES;
}

- (void)freeQureyResultTable:(char **)result{
	sqlite3_free_table(result);
}//调用查询结果后，必须调用该函数进行内存释放

- (BOOL)dataFileFromAppToTempDir:(NSString *)fullFileName fileName:(NSString *)fileName fileExec:(NSString *)fileExec {
	
    //	NSString * tempDir = [FCPathUtility documentsPath];
	NSString * dataBasePath = [DOCUMENTS_DB stringByAppendingPathComponent:fullFileName];
	NSLog(@"%@",dataBasePath);
    
	NSFileManager * fileManage = [NSFileManager defaultManager];
    
	BOOL ret = YES;
	if ([fileManage fileExistsAtPath:dataBasePath] == NO) {//dataBase  not exist
		
		NSString * dataBaseUnderApp = [[NSBundle mainBundle] pathForResource:fileName ofType:fileExec];
		NSLog(@"%@", dataBaseUnderApp);
		NSError * errorCopy = nil;
		if ([fileManage fileExistsAtPath:dataBaseUnderApp] == NO) {//create dataBase file
			if ([fileManage createFileAtPath:dataBasePath contents:nil attributes:nil] == NO) {
				ret = NO;
			}
		}
		else {//copy dataBaseFile to tmp directory
			if([fileManage copyItemAtPath:dataBaseUnderApp toPath:dataBasePath error:&errorCopy] == NO){
				NSLog(@"%@", [errorCopy localizedDescription]);
				ret = NO;
			}
		}
	}

	return ret;
}

- (BOOL)isRecordExisit:(NSString *)tableName where:(NSString *)where{
	NSString * strSql = [NSString stringWithFormat:@"select * from %@ where %@",tableName,where];
	sqlite3_stmt * stateMent;
	BOOL ret = YES;
	if (sqlite3_prepare_v2(_database, [strSql UTF8String], -1, &stateMent, nil) != SQLITE_OK) {
		ret = NO;
	}
	
	if (sqlite3_step(stateMent) != SQLITE_ROW) {
		ret = NO;
	}
	
	sqlite3_finalize(stateMent);
	return ret;
}

- (BOOL)isRecordExisit:(NSString *)strsql {
	
	sqlite3_stmt * stateMent;
	BOOL ret = YES;
	if (sqlite3_prepare_v2(_database, [strsql UTF8String], -1, &stateMent, nil) != SQLITE_OK) {
		ret = NO;
	}
	
	if (sqlite3_step(stateMent) != SQLITE_ROW) {
		ret = NO;
	}
	
	sqlite3_finalize(stateMent);
	return ret;
}

- (BOOL)startTransaction {
	if (_isDbOpend == NO) {
		return NO;
	}
    
    char *error = nil;
    int ret = sqlite3_exec(_database, "begin transaction", 0, 0, &error);
	if (ret != SQLITE_OK) {
		//NSAssert1(0,@"failed to update the database because:%s",errorMsg);
        NSLog(@"lun failed to startTransaction the database because:%s", error);
		return NO;
	}
	return YES;
}

- (BOOL)commitTransaction {
	if (_isDbOpend == NO) {
		return NO;
	}
    
    char *error = nil;
    int ret = sqlite3_exec(_database, "commit transaction", 0, 0, &error);
	if (ret != SQLITE_OK) {
		//NSAssert1(0,@"failed to update the database because:%s",errorMsg);
        NSLog(@"lun failed to commitTransaction the database because:%s", error);
		return NO;
	}
	return YES;
}
- (BOOL)rollBackTransaction {
	if (_isDbOpend == NO) {
		return NO;
	}

    char *error = nil;
    int ret = sqlite3_exec(_database, "rollback transaction", 0, 0, &error);
	if (ret != SQLITE_OK) {
		//NSAssert1(0,@"failed to update the database because:%s",errorMsg);
        NSLog(@"lun failed to rollBackTransaction the database because:%s", error);
		return NO;
	}
	return YES;
}

//////////////業務開始////////////
- (lxbaseEntity *)getAppInfo {
    lxAppInfoEntity *appInfo = nil;

	if (NO == _isDbOpend)
	{
		if ([self openDataBase] == NO)
		{
			return appInfo;
		}
	}
	NSString* strSql = selectAppInfo;
	NSLog(@"%@", strSql);
	sqlite3_stmt *statement = nil;
	if (sqlite3_prepare_v2(_database, [strSql UTF8String], -1, &statement, nil) == SQLITE_OK)
	{
		while (sqlite3_step(statement) == SQLITE_ROW)
		{
            appInfo = [[lxAppInfoEntity alloc] init];
            appInfo.name = [self Null2NSString:statement column:0];
            appInfo.version = [self Null2NSString:statement column:1];
            sqlite3_finalize(statement);
            return appInfo;
		}
	}
    return appInfo;
}

- (lxbaseEntity *)getUserInfo {
    lxUserInfoEntity *userInfo = nil;
    
    if (NO == _isDbOpend)
	{
		if ([self openDataBase] == NO)
		{
			return userInfo;
		}
	}
	NSString* strSql = selectUserInfo;
	NSLog(@"%@", strSql);
	sqlite3_stmt *statement = nil;
	if (sqlite3_prepare_v2(_database, [strSql UTF8String], -1, &statement, nil) == SQLITE_OK)
	{
		while (sqlite3_step(statement) == SQLITE_ROW)
		{
            userInfo = [[lxUserInfoEntity alloc] init];
            
            userInfo.user_id = sqlite3_column_int(statement, 0);
            userInfo.email = [self Null2NSString:statement column:1];
            userInfo.user_name = [self Null2NSString:statement column:2];
            userInfo.password = [self Null2NSString:statement column:3];
            userInfo.question = [self Null2NSString:statement column:4];
            userInfo.answer = [self Null2NSString:statement column:5];
            int sex = sqlite3_column_int(statement, 6);
            userInfo.sex = NO;
            if (0 != sex) {
                userInfo.sex = YES;
            }
            userInfo.birthday = [self Null2NSString:statement column:7];
            userInfo.user_money = sqlite3_column_double(statement, 8);
            userInfo.frozen_money = sqlite3_column_double(statement, 9);
            userInfo.pay_points = sqlite3_column_int(statement, 10);
            userInfo.rank_points = sqlite3_column_int(statement, 11);
            userInfo.address_id = sqlite3_column_int(statement, 12);
            userInfo.reg_time = [self Null2NSString:statement column:13];
            userInfo.last_login = [self Null2NSString:statement column:14];
            userInfo.last_time = [self Null2NSString:statement column:15];
            userInfo.last_ip = [self Null2NSString:statement column:16];
            userInfo.visit_count = sqlite3_column_int(statement, 17);
            userInfo.user_rank = sqlite3_column_int(statement, 18);
            userInfo.is_special = sqlite3_column_int(statement, 19);
            userInfo.salt = [self Null2NSString:statement column:20];
            userInfo.parent_id = sqlite3_column_int(statement, 21);
            userInfo.flag = sqlite3_column_int(statement, 22);
            userInfo.alias = [self Null2NSString:statement column:23];
            userInfo.msn = [self Null2NSString:statement column:24];
            userInfo.qq = [self Null2NSString:statement column:25];
            userInfo.office_phone = [self Null2NSString:statement column:26];
            userInfo.home_phone = [self Null2NSString:statement column:27];
            userInfo.mobile_phone = [self Null2NSString:statement column:28];
            userInfo.is_validated = sqlite3_column_int(statement, 29);
            userInfo.credit_line = sqlite3_column_double(statement, 30);
            userInfo.passwd_question = [self Null2NSString:statement column:31];
            userInfo.passwd_answer = [self Null2NSString:statement column:32];
            userInfo.user_bonus = sqlite3_column_double(statement, 33);
            
            sqlite3_finalize(statement);
            return userInfo;
		}
	}
    return userInfo;
}

- (BOOL)setAppInfo:(lxbaseEntity *)entity {
    BOOL ret = NO;
    if (nil == entity)
	{
		return ret;
	}
    
    lxAppInfoEntity *appInfo = (lxAppInfoEntity *)entity;
    
    appInfo.name = [lxStringUtility stringTrim:appInfo.name];
    if (YES == [lxStringUtility isNil:appInfo.name]) {
        return ret;
    }
    
    appInfo.version = [lxStringUtility stringTrim:appInfo.version];
    if (YES == [lxStringUtility isNil:appInfo.version]) {
        return ret;
    }
    
	if (!_isDbOpend)
	{
		if ([self openDataBase] == NO)
		{
			return ret;
		}
		
	}

    ret = [self startTransaction];
    if (NO == ret) {
        return ret;
    }
    
    NSString *strSql = delAppInfo;
    
    ret = [self execute:strSql];
    if (NO == ret) {
        [self rollBackTransaction];
        return ret;
    }
    
    NSString *values = [NSString stringWithFormat:@"'%@', '%@'", appInfo.name, appInfo.version];
    strSql = [NSString stringWithFormat:insertAppInfo, values];

    ret = [self execute:strSql];
    if (NO == ret) {
        [self rollBackTransaction];
        return ret;
    }
    
    ret = [self commitTransaction];
    if (NO == ret) {
        [self rollBackTransaction];
        return ret;
    }

	return ret;
}

- (BOOL)setUserInfo:(lxbaseEntity *)entity {
    BOOL ret = NO;
    if (nil == entity)
	{
		return ret;
	}
    
    lxUserInfoEntity *userInfo = (lxUserInfoEntity *)entity;
    
    userInfo.email = [lxStringUtility stringTrim:userInfo.email];
    if (YES == [lxStringUtility isNil:userInfo.email]) {
        return ret;
    }
    
    userInfo.user_name = [lxStringUtility stringTrim:userInfo.user_name];
    if (YES == [lxStringUtility isNil:userInfo.user_name]) {
        return ret;
    }
    
    userInfo.password = [lxStringUtility stringTrim:userInfo.password];
    if (YES == [lxStringUtility isNil:userInfo.password]) {
        return ret;
    }
    
	if (!_isDbOpend)
	{
		if ([self openDataBase] == NO)
		{
			return ret;
		}
		
	}
    
    ret = [self startTransaction];
    if (NO == ret) {
        return ret;
    }
    
    NSString* strSql = delUserInfo;
    
    ret = [self execute:strSql];
    if (NO == ret) {
        [self rollBackTransaction];
        return ret;
    }
    
    NSString *values = [NSString stringWithFormat:@"'%d', '%@', '%@', '%@', '%@', '%@', '%d', '%@', '%10.2f', '%10.2f', '%d', '%d', '%d', '%@', '%@', '%@', '%@', '%d', '%d', '%d', '%@', '%d', '%d', '%@', '%@', '%@', '%@', '%@', '%@', '%d', '%10.2f', '%@', '%@', '%10.2f'",
                        userInfo.user_id,
                        userInfo.email,
                        userInfo.user_name,
                        userInfo.password,
                        userInfo.question,
                        userInfo.answer,
                        userInfo.sex,
                        userInfo.birthday,
                        userInfo.user_money,
                        userInfo.frozen_money,
                        userInfo.pay_points,
                        userInfo.rank_points,
                        userInfo.address_id,
                        userInfo.reg_time,
                        userInfo.last_login,
                        userInfo.last_time,
                        userInfo.last_ip,
                        userInfo.visit_count,
                        userInfo.user_rank,
                        userInfo.is_special,
                        userInfo.salt,
                        userInfo.parent_id,
                        userInfo.flag,
                        userInfo.alias,
                        userInfo.msn,
                        userInfo.qq,
                        userInfo.office_phone,
                        userInfo.home_phone,
                        userInfo.mobile_phone,
                        userInfo.is_validated,
                        userInfo.credit_line,
                        userInfo.passwd_question,
                        userInfo.passwd_answer,
                        userInfo.user_bonus];
    strSql = [NSString stringWithFormat:insertUserInfo, values];

    ret = [self execute:strSql];
    if (NO == ret) {
        [self rollBackTransaction];
        return ret;
    }
    
    ret = [self commitTransaction];
    if (NO == ret) {
        [self rollBackTransaction];
        return ret;
    }
    
	return ret;
}

- (BOOL)delUser {
    BOOL ret = NO;
    
	if (!_isDbOpend)
	{
		if ([self openDataBase] == NO)
		{
			return ret;
		}
		
	}
    
    NSString* strSql = delUserInfo;
    
    ret = [self execute:strSql];
    
	return ret;
}

@end
